#-*-coding:utf-8-*-
import time
import psycopg2
import json
import datetime

class DB_handler(object):

    def __init__(self):
        super(DB_handler,self).__init__()


        # self.cgtw_connect = psycopg2.connect(database='taskprogress', user='timeaxis', password='timeaxis',
        #                                 host='192.168.1.222',port=5432)
        #
        # self.cgtw_cur =self.cgtw_connect.cursor()

        self.time_connect=psycopg2.connect(database='taskprogress', user='timeaxis', password='timeaxis',
                                        host='192.168.1.222',port=5432)
        self.time_cur=self.time_connect.cursor()

    def close_all(self):
        """
        关闭所有连接
        :return:
        """
        # self.cgtw_connect.close()
        self.time_connect.close()


    def add_artist_ip(self,artist,department,ip):
        sql="insert into account (artist,department,ip) values('{0}','{1}','{2}') on conflict(ip) do update set artist='{0}'".format(artist,department,ip)
        self.time_cur.execute(sql)

    def update_notice(self,notice):
        # sql="insert into config (notice) values('{0}') ".format(notice)#on conflict(id) do update set notice='{0}'
        sql=" update config set notice='{0}' where id = 1 ".format(notice)
        self.time_cur.execute(sql)

    # def set_column_value(self,column_name,value):
    #     sql="update daily set {} = '{}' pass_task=0 where pass_task is null".format(column_name,value)
    #     self.time_cur.execute(sql)
    def set_pass_task_not_null(self):
        sql="update daily set pass_task=0 where pass_task is null"
        self.time_cur.execute(sql)

    def patch_department(self):
        """
        将note表中的department填上
        :return:
        """
        select_sql="select artist,department from account"
        try:
            self.time_cur.execute(select_sql)
            result=self.time_cur.fetchall()
            if result:
                department_map={}
                for i in result:
                    artist=i[0]
                    department=i[1]
                    if artist not in department_map.keys():
                        department_map[artist]=department
                if department_map:
                    print('department_map:',department_map)
                    for ar,de in department_map.items():
                        update_sql="update note set department ='{}' where artist = '{}'".format(de,ar)
                        self.time_cur.execute(update_sql)
                    self.time_connect.commit()
        except Exception as patch_department_ERR:
            print('patch_department_ERR:',str(patch_department_ERR))
        # finally:
        #     self.close_all()



    def test(self):
        # sql=" if exists (select * from daily where cast(r_id as character varying)||date = '2030352020-10-12')  " \
        #           "then  " \
        #           "update daily set assigned_score ='4' " \
        #           "end if"

        sql="""
            if exists(select * from daily where cast(r_id as character varying)||date = '2030352020-10-12') 
            then update daily set assigned_score ='4' 
            end if
        """
        # sql = "if exists (select * from daily where cast(r_id as character varying)||date = '2030352020-10-12') "
        # sql="if(select count(*) from daily where date='2020-10-12')>0 then update daily set assigned_score ='5'"
        self.time_cur.execute(sql)


if __name__ == '__main__':
    db_hander=DB_handler()
    try:
        db_hander.add_artist_ip('邴楠','192.168.1.93')
        # db_hander.update_notice('广告位,暂无信息')
        # db_hander.test()
        db_hander.patch_department()
        db_hander.time_connect.commit()
        print('添加成功')
    except Exception as add_ERR:
        print(str(add_ERR))
    finally:
        db_hander.close_all()


